Q-4: SQLite Database Applications (5 Marks)

Questions

i) Create a simple android application to insert and update data in student table of PPSU SQLite database.

ii) Create a simple android application to delete and view data from voters table of Election SQLite database.

---

Answers

i) Student Management Application - PPSU SQLite Database

Step 1: Database Helper Class

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;

public class PPSUDatabaseHelper extends SQLiteOpenHelper {
    // Database constants
    private static final String DATABASE_NAME = "PPSU_Database.db";
    private static final int DATABASE_VERSION = 1;
    
    // Student table constants
    private static final String TABLE_STUDENT = "student";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_ROLL_NO = "roll_number";
    private static final String COLUMN_BRANCH = "branch";
    private static final String COLUMN_SEMESTER = "semester";
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_PHONE = "phone";

    public PPSUDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createStudentTable = "CREATE TABLE " + TABLE_STUDENT + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_NAME + " TEXT NOT NULL, " +
            COLUMN_ROLL_NO + " TEXT UNIQUE NOT NULL, " +
            COLUMN_BRANCH + " TEXT NOT NULL, " +
            COLUMN_SEMESTER + " INTEGER NOT NULL, " +
            COLUMN_EMAIL + " TEXT UNIQUE, " +
            COLUMN_PHONE + " TEXT)";
        
        db.execSQL(createStudentTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENT);
        onCreate(db);
    }

    // Insert student data
    public long insertStudent(Student student) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(COLUMN_NAME, student.getName());
        values.put(COLUMN_ROLL_NO, student.getRollNumber());
        values.put(COLUMN_BRANCH, student.getBranch());
        values.put(COLUMN_SEMESTER, student.getSemester());
        values.put(COLUMN_EMAIL, student.getEmail());
        values.put(COLUMN_PHONE, student.getPhone());
        
        long result = db.insert(TABLE_STUDENT, null, values);
        db.close();
        return result;
    }

    // Update student data
    public int updateStudent(Student student) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(COLUMN_NAME, student.getName());
        values.put(COLUMN_BRANCH, student.getBranch());
        values.put(COLUMN_SEMESTER, student.getSemester());
        values.put(COLUMN_EMAIL, student.getEmail());
        values.put(COLUMN_PHONE, student.getPhone());
        
        int result = db.update(TABLE_STUDENT, values, 
            COLUMN_ID + " = ?", new String[]{String.valueOf(student.getId())});
        db.close();
        return result;
    }

    // Get all students
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_STUDENT + " ORDER BY " + COLUMN_NAME, null);
        
        if (cursor.moveToFirst()) {
            do {
                Student student = new Student();
                student.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                student.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
                student.setRollNumber(cursor.getString(cursor.getColumnIndex(COLUMN_ROLL_NO)));
                student.setBranch(cursor.getString(cursor.getColumnIndex(COLUMN_BRANCH)));
                student.setSemester(cursor.getInt(cursor.getColumnIndex(COLUMN_SEMESTER)));
                student.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_EMAIL)));
                student.setPhone(cursor.getString(cursor.getColumnIndex(COLUMN_PHONE)));
                
                students.add(student);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return students;
    }

    // Get student by roll number
    public Student getStudentByRollNumber(String rollNumber) {
        SQLiteDatabase db = this.getReadableDatabase();
        Student student = null;
        
        Cursor cursor = db.query(TABLE_STUDENT, null, COLUMN_ROLL_NO + " = ?", 
            new String[]{rollNumber}, null, null, null);
        
        if (cursor.moveToFirst()) {
            student = new Student();
            student.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
            student.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
            student.setRollNumber(cursor.getString(cursor.getColumnIndex(COLUMN_ROLL_NO)));
            student.setBranch(cursor.getString(cursor.getColumnIndex(COLUMN_BRANCH)));
            student.setSemester(cursor.getInt(cursor.getColumnIndex(COLUMN_SEMESTER)));
            student.setEmail(cursor.getString(cursor.getColumnIndex(COLUMN_EMAIL)));
            student.setPhone(cursor.getString(cursor.getColumnIndex(COLUMN_PHONE)));
        }
        
        cursor.close();
        db.close();
        return student;
    }
}

Step 2: Student Model Class

public class Student {
    private int id;
    private String name;
    private String rollNumber;
    private String branch;
    private int semester;
    private String email;
    private String phone;

    // Default constructor
    public Student() {}

    // Parameterized constructor
    public Student(String name, String rollNumber, String branch, int semester, String email, String phone) {
        this.name = name;
        this.rollNumber = rollNumber;
        this.branch = branch;
        this.semester = semester;
        this.email = email;
        this.phone = phone;
    }

    // Getters and Setters
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public String getRollNumber() { return rollNumber; }
    public void setRollNumber(String rollNumber) { this.rollNumber = rollNumber; }
    
    public String getBranch() { return branch; }
    public void setBranch(String branch) { this.branch = branch; }
    
    public int getSemester() { return semester; }
    public void setSemester(int semester) { this.semester = semester; }
    
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    
    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }

    @Override
    public String toString() {
        return "ID: " + id + "\nName: " + name + "\nRoll No: " + rollNumber + 
               "\nBranch: " + branch + "\nSemester: " + semester + 
               "\nEmail: " + email + "\nPhone: " + phone;
    }
}

Step 3: Main Activity Layout (activity_main.xml)

<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:padding="16dp">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical">

        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="PPSU Student Management"
            android:textSize="24sp"
            android:textStyle="bold"
            android:gravity="center"
            android:layout_marginBottom="24dp" />

        <EditText
            android:id="@+id/editTextName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Student Name"
            android:layout_marginBottom="12dp" />

        <EditText
            android:id="@+id/editTextRollNumber"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Roll Number"
            android:layout_marginBottom="12dp" />

        <Spinner
            android:id="@+id/spinnerBranch"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginBottom="12dp" />

        <EditText
            android:id="@+id/editTextSemester"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Semester"
            android:inputType="number"
            android:layout_marginBottom="12dp" />

        <EditText
            android:id="@+id/editTextEmail"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Email Address"
            android:inputType="textEmailAddress"
            android:layout_marginBottom="12dp" />

        <EditText
            android:id="@+id/editTextPhone"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Phone Number"
            android:inputType="phone"
            android:layout_marginBottom="24dp" />

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:layout_marginBottom="24dp">

            <Button
                android:id="@+id/btnInsert"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Insert"
                android:layout_marginEnd="8dp" />

            <Button
                android:id="@+id/btnUpdate"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Update"
                android:layout_marginStart="8dp" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:layout_marginBottom="24dp">

            <Button
                android:id="@+id/btnSearch"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Search"
                android:layout_marginEnd="8dp" />

            <Button
                android:id="@+id/btnClear"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Clear"
                android:layout_marginStart="8dp" />
        </LinearLayout>

        <TextView
            android:id="@+id/textViewResult"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Student Information will appear here"
            android:background="@android:color/lighter_gray"
            android:padding="12dp"
            android:minHeight="100dp" />

    </LinearLayout>
</ScrollView>

Step 4: MainActivity Implementation

import android.os.Bundle;
import android.view.View;
import android.widget.*;
import androidx.appcompat.app.AppCompatActivity;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    private PPSUDatabaseHelper dbHelper;
    private EditText editTextName, editTextRollNumber, editTextSemester, editTextEmail, editTextPhone;
    private Spinner spinnerBranch;
    private TextView textViewResult;
    private Student currentStudent = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        initializeViews();
        setupBranchSpinner();
        setupClickListeners();
        
        dbHelper = new PPSUDatabaseHelper(this);
        displayAllStudents();
    }

    private void initializeViews() {
        editTextName = findViewById(R.id.editTextName);
        editTextRollNumber = findViewById(R.id.editTextRollNumber);
        editTextSemester = findViewById(R.id.editTextSemester);
        editTextEmail = findViewById(R.id.editTextEmail);
        editTextPhone = findViewById(R.id.editTextPhone);
        spinnerBranch = findViewById(R.id.spinnerBranch);
        textViewResult = findViewById(R.id.textViewResult);
    }

    private void setupBranchSpinner() {
        String[] branches = {"Computer Engineering", "Information Technology", 
                           "Electronics & Communication", "Mechanical Engineering", 
                           "Civil Engineering", "Electrical Engineering"};
        
        ArrayAdapter<String> adapter = new ArrayAdapter<>(this, 
            android.R.layout.simple_spinner_item, branches);
        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        spinnerBranch.setAdapter(adapter);
    }

    private void setupClickListeners() {
        findViewById(R.id.btnInsert).setOnClickListener(v -> insertStudent());
        findViewById(R.id.btnUpdate).setOnClickListener(v -> updateStudent());
        findViewById(R.id.btnSearch).setOnClickListener(v -> searchStudent());
        findViewById(R.id.btnClear).setOnClickListener(v -> clearFields());
    }

    private void insertStudent() {
        if (validateInputs()) {
            Student student = createStudentFromInputs();
            long result = dbHelper.insertStudent(student);
            
            if (result != -1) {
                Toast.makeText(this, "Student inserted successfully", Toast.LENGTH_SHORT).show();
                clearFields();
                displayAllStudents();
            } else {
                Toast.makeText(this, "Failed to insert student", Toast.LENGTH_SHORT).show();
            }
        }
    }

    private void updateStudent() {
        if (currentStudent != null && validateInputs()) {
            currentStudent.setName(editTextName.getText().toString().trim());
            currentStudent.setBranch(spinnerBranch.getSelectedItem().toString());
            currentStudent.setSemester(Integer.parseInt(editTextSemester.getText().toString().trim()));
            currentStudent.setEmail(editTextEmail.getText().toString().trim());
            currentStudent.setPhone(editTextPhone.getText().toString().trim());
            
            int result = dbHelper.updateStudent(currentStudent);
            
            if (result > 0) {
                Toast.makeText(this, "Student updated successfully", Toast.LENGTH_SHORT).show();
                clearFields();
                displayAllStudents();
                currentStudent = null;
            } else {
                Toast.makeText(this, "Failed to update student", Toast.LENGTH_SHORT).show();
            }
        } else {
            Toast.makeText(this, "Please search for a student first", Toast.LENGTH_SHORT).show();
        }
    }

    private void searchStudent() {
        String rollNumber = editTextRollNumber.getText().toString().trim();
        if (!rollNumber.isEmpty()) {
            Student student = dbHelper.getStudentByRollNumber(rollNumber);
            
            if (student != null) {
                currentStudent = student;
                populateFields(student);
                textViewResult.setText("Student Found:\n" + student.toString());
            } else {
                Toast.makeText(this, "Student not found", Toast.LENGTH_SHORT).show();
                textViewResult.setText("No student found with roll number: " + rollNumber);
            }
        } else {
            Toast.makeText(this, "Please enter roll number", Toast.LENGTH_SHORT).show();
        }
    }

    private void populateFields(Student student) {
        editTextName.setText(student.getName());
        editTextRollNumber.setText(student.getRollNumber());
        editTextSemester.setText(String.valueOf(student.getSemester()));
        editTextEmail.setText(student.getEmail());
        editTextPhone.setText(student.getPhone());
        
        // Set spinner selection
        ArrayAdapter<String> adapter = (ArrayAdapter<String>) spinnerBranch.getAdapter();
        int position = adapter.getPosition(student.getBranch());
        spinnerBranch.setSelection(position);
    }

    private void clearFields() {
        editTextName.setText("");
        editTextRollNumber.setText("");
        editTextSemester.setText("");
        editTextEmail.setText("");
        editTextPhone.setText("");
        spinnerBranch.setSelection(0);
        currentStudent = null;
        displayAllStudents();
    }

    private void displayAllStudents() {
        List<Student> students = dbHelper.getAllStudents();
        StringBuilder sb = new StringBuilder();
        
        if (!students.isEmpty()) {
            sb.append("All Students in Database:\n\n");
            for (Student student : students) {
                sb.append(student.toString()).append("\n\n");
            }
        } else {
            sb.append("No students found in database");
        }
        
        textViewResult.setText(sb.toString());
    }

    private boolean validateInputs() {
        if (editTextName.getText().toString().trim().isEmpty()) {
            Toast.makeText(this, "Please enter student name", Toast.LENGTH_SHORT).show();
            return false;
        }
        
        if (editTextRollNumber.getText().toString().trim().isEmpty()) {
            Toast.makeText(this, "Please enter roll number", Toast.LENGTH_SHORT).show();
            return false;
        }
        
        if (editTextSemester.getText().toString().trim().isEmpty()) {
            Toast.makeText(this, "Please enter semester", Toast.LENGTH_SHORT).show();
            return false;
        }
        
        return true;
    }

    private Student createStudentFromInputs() {
        return new Student(
            editTextName.getText().toString().trim(),
            editTextRollNumber.getText().toString().trim(),
            spinnerBranch.getSelectedItem().toString(),
            Integer.parseInt(editTextSemester.getText().toString().trim()),
            editTextEmail.getText().toString().trim(),
            editTextPhone.getText().toString().trim()
        );
    }
}

---

ii) Voters Management Application - Election SQLite Database

Step 1: Election Database Helper Class

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;

public class ElectionDatabaseHelper extends SQLiteOpenHelper {
    // Database constants
    private static final String DATABASE_NAME = "Election_Database.db";
    private static final int DATABASE_VERSION = 1;
    
    // Voters table constants
    private static final String TABLE_VOTERS = "voters";
    private static final String COLUMN_VOTER_ID = "voter_id";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_AGE = "age";
    private static final String COLUMN_ADDRESS = "address";
    private static final String COLUMN_PHONE = "phone";
    private static final String COLUMN_CONSTITUENCY = "constituency";
    private static final String COLUMN_HAS_VOTED = "has_voted";
    private static final String COLUMN_REGISTRATION_DATE = "registration_date";

    public ElectionDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createVotersTable = "CREATE TABLE " + TABLE_VOTERS + " (" +
            COLUMN_VOTER_ID + " TEXT PRIMARY KEY, " +
            COLUMN_NAME + " TEXT NOT NULL, " +
            COLUMN_AGE + " INTEGER NOT NULL, " +
            COLUMN_ADDRESS + " TEXT NOT NULL, " +
            COLUMN_PHONE + " TEXT, " +
            COLUMN_CONSTITUENCY + " TEXT NOT NULL, " +
            COLUMN_HAS_VOTED + " INTEGER DEFAULT 0, " +
            COLUMN_REGISTRATION_DATE + " TEXT)";
        
        db.execSQL(createVotersTable);
        
        // Insert sample data
        insertSampleData(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_VOTERS);
        onCreate(db);
    }

    private void insertSampleData(SQLiteDatabase db) {
        String[] sampleData = {
            "INSERT INTO " + TABLE_VOTERS + " VALUES ('V001', 'John Doe', 25, '123 Main St', '9876543210', 'North Constituency', 0, '2024-01-15')",
            "INSERT INTO " + TABLE_VOTERS + " VALUES ('V002', 'Jane Smith', 32, '456 Oak Ave', '9876543211', 'South Constituency', 1, '2024-01-10')",
            "INSERT INTO " + TABLE_VOTERS + " VALUES ('V003', 'Mike Johnson', 45, '789 Pine Rd', '9876543212', 'East Constituency', 0, '2024-01-20')",
            "INSERT INTO " + TABLE_VOTERS + " VALUES ('V004', 'Sarah Williams', 28, '321 Elm St', '9876543213', 'West Constituency', 1, '2024-01-05')",
            "INSERT INTO " + TABLE_VOTERS + " VALUES ('V005', 'David Brown', 55, '654 Maple Dr', '9876543214', 'North Constituency', 0, '2024-01-12')"
        };
        
        for (String sql : sampleData) {
            db.execSQL(sql);
        }
    }

    // Delete voter by ID
    public int deleteVoter(String voterId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_VOTERS, COLUMN_VOTER_ID + " = ?", new String[]{voterId});
        db.close();
        return result;
    }

    // Get all voters
    public List<Voter> getAllVoters() {
        List<Voter> voters = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_VOTERS + " ORDER BY " + COLUMN_NAME, null);
        
        if (cursor.moveToFirst()) {
            do {
                Voter voter = new Voter();
                voter.setVoterId(cursor.getString(cursor.getColumnIndex(COLUMN_VOTER_ID)));
                voter.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
                voter.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));
                voter.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
                voter.setPhone(cursor.getString(cursor.getColumnIndex(COLUMN_PHONE)));
                voter.setConstituency(cursor.getString(cursor.getColumnIndex(COLUMN_CONSTITUENCY)));
                voter.setHasVoted(cursor.getInt(cursor.getColumnIndex(COLUMN_HAS_VOTED)) == 1);
                voter.setRegistrationDate(cursor.getString(cursor.getColumnIndex(COLUMN_REGISTRATION_DATE)));
                
                voters.add(voter);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return voters;
    }

    // Get voter by ID
    public Voter getVoterById(String voterId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Voter voter = null;
        
        Cursor cursor = db.query(TABLE_VOTERS, null, COLUMN_VOTER_ID + " = ?", 
            new String[]{voterId}, null, null, null);
        
        if (cursor.moveToFirst()) {
            voter = new Voter();
            voter.setVoterId(cursor.getString(cursor.getColumnIndex(COLUMN_VOTER_ID)));
            voter.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
            voter.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));
            voter.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
            voter.setPhone(cursor.getString(cursor.getColumnIndex(COLUMN_PHONE)));
            voter.setConstituency(cursor.getString(cursor.getColumnIndex(COLUMN_CONSTITUENCY)));
            voter.setHasVoted(cursor.getInt(cursor.getColumnIndex(COLUMN_HAS_VOTED)) == 1);
            voter.setRegistrationDate(cursor.getString(cursor.getColumnIndex(COLUMN_REGISTRATION_DATE)));
        }
        
        cursor.close();
        db.close();
        return voter;
    }

    // Get voters by constituency
    public List<Voter> getVotersByConstituency(String constituency) {
        List<Voter> voters = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_VOTERS, null, COLUMN_CONSTITUENCY + " = ?", 
            new String[]{constituency}, null, null, COLUMN_NAME);
        
        if (cursor.moveToFirst()) {
            do {
                Voter voter = new Voter();
                voter.setVoterId(cursor.getString(cursor.getColumnIndex(COLUMN_VOTER_ID)));
                voter.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
                voter.setAge(cursor.getInt(cursor.getColumnIndex(COLUMN_AGE)));
                voter.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
                voter.setPhone(cursor.getString(cursor.getColumnIndex(COLUMN_PHONE)));
                voter.setConstituency(cursor.getString(cursor.getColumnIndex(COLUMN_CONSTITUENCY)));
                voter.setHasVoted(cursor.getInt(cursor.getColumnIndex(COLUMN_HAS_VOTED)) == 1);
                voter.setRegistrationDate(cursor.getString(cursor.getColumnIndex(COLUMN_REGISTRATION_DATE)));
                
                voters.add(voter);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return voters;
    }

    // Get voting statistics
    public VotingStats getVotingStats() {
        SQLiteDatabase db = this.getReadableDatabase();
        VotingStats stats = new VotingStats();
        
        // Total voters
        Cursor totalCursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_VOTERS, null);
        if (totalCursor.moveToFirst()) {
            stats.setTotalVoters(totalCursor.getInt(0));
        }
        totalCursor.close();
        
        // Voted count
        Cursor votedCursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_VOTERS + 
            " WHERE " + COLUMN_HAS_VOTED + " = 1", null);
        if (votedCursor.moveToFirst()) {
            stats.setVotedCount(votedCursor.getInt(0));
        }
        votedCursor.close();
        
        // Not voted count
        stats.setNotVotedCount(stats.getTotalVoters() - stats.getVotedCount());
        
        // Voting percentage
        if (stats.getTotalVoters() > 0) {
            stats.setVotingPercentage((double) stats.getVotedCount() / stats.getTotalVoters() * 100);
        }
        
        db.close();
        return stats;
    }
}

Step 2: Voter Model Classes

// Voter.java
public class Voter {
    private String voterId;
    private String name;
    private int age;
    private String address;
    private String phone;
    private String constituency;
    private boolean hasVoted;
    private String registrationDate;

    // Constructors, getters and setters
    public Voter() {}

    public Voter(String voterId, String name, int age, String address, String phone, 
                String constituency, boolean hasVoted, String registrationDate) {
        this.voterId = voterId;
        this.name = name;
        this.age = age;
        this.address = address;
        this.phone = phone;
        this.constituency = constituency;
        this.hasVoted = hasVoted;
        this.registrationDate = registrationDate;
    }

    // Getters and Setters
    public String getVoterId() { return voterId; }
    public void setVoterId(String voterId) { this.voterId = voterId; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    
    public String getAddress() { return address; }
    public void setAddress(String address) { this.address = address; }
    
    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }
    
    public String getConstituency() { return constituency; }
    public void setConstituency(String constituency) { this.constituency = constituency; }
    
    public boolean isHasVoted() { return hasVoted; }
    public void setHasVoted(boolean hasVoted) { this.hasVoted = hasVoted; }
    
    public String getRegistrationDate() { return registrationDate; }
    public void setRegistrationDate(String registrationDate) { this.registrationDate = registrationDate; }

    @Override
    public String toString() {
        return "Voter ID: " + voterId + "\nName: " + name + "\nAge: " + age + 
               "\nAddress: " + address + "\nPhone: " + phone + 
               "\nConstituency: " + constituency + "\nVoted: " + (hasVoted ? "Yes" : "No") +
               "\nRegistration Date: " + registrationDate;
    }
}

// VotingStats.java
public class VotingStats {
    private int totalVoters;
    private int votedCount;
    private int notVotedCount;
    private double votingPercentage;

    // Constructors, getters and setters
    public VotingStats() {}

    public int getTotalVoters() { return totalVoters; }
    public void setTotalVoters(int totalVoters) { this.totalVoters = totalVoters; }
    
    public int getVotedCount() { return votedCount; }
    public void setVotedCount(int votedCount) { this.votedCount = votedCount; }
    
    public int getNotVotedCount() { return notVotedCount; }
    public void setNotVotedCount(int notVotedCount) { this.notVotedCount = notVotedCount; }
    
    public double getVotingPercentage() { return votingPercentage; }
    public void setVotingPercentage(double votingPercentage) { this.votingPercentage = votingPercentage; }

    @Override
    public String toString() {
        return String.format("Election Statistics:\nTotal Voters: %d\nVoted: %d\nNot Voted: %d\nVoting Percentage: %.2f%%",
               totalVoters, votedCount, notVotedCount, votingPercentage);
    }
}

Step 3: Election MainActivity Implementation

import android.os.Bundle;
import android.widget.*;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;
import java.util.List;

public class ElectionMainActivity extends AppCompatActivity {
    private ElectionDatabaseHelper dbHelper;
    private EditText editTextVoterId;
    private Spinner spinnerConstituency;
    private TextView textViewResult;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_election_main);

        initializeViews();
        setupConstituencySpinner();
        setupClickListeners();
        
        dbHelper = new ElectionDatabaseHelper(this);
        displayAllVoters();
    }

    private void initializeViews() {
        editTextVoterId = findViewById(R.id.editTextVoterId);
        spinnerConstituency = findViewById(R.id.spinnerConstituency);
        textViewResult = findViewById(R.id.textViewResult);
    }

    private void setupConstituencySpinner() {
        String[] constituencies = {"All Constituencies", "North Constituency", 
                                 "South Constituency", "East Constituency", "West Constituency"};
        
        ArrayAdapter<String> adapter = new ArrayAdapter<>(this, 
            android.R.layout.simple_spinner_item, constituencies);
        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        spinnerConstituency.setAdapter(adapter);
    }

    private void setupClickListeners() {
        findViewById(R.id.btnViewAll).setOnClickListener(v -> displayAllVoters());
        findViewById(R.id.btnViewByConstituency).setOnClickListener(v -> viewByConstituency());
        findViewById(R.id.btnSearchVoter).setOnClickListener(v -> searchVoter());
        findViewById(R.id.btnDeleteVoter).setOnClickListener(v -> confirmDeleteVoter());
        findViewById(R.id.btnShowStats).setOnClickListener(v -> showVotingStats());
    }

    private void displayAllVoters() {
        List<Voter> voters = dbHelper.getAllVoters();
        displayVotersList(voters, "All Voters");
    }

    private void viewByConstituency() {
        String selectedConstituency = spinnerConstituency.getSelectedItem().toString();
        
        if (selectedConstituency.equals("All Constituencies")) {
            displayAllVoters();
        } else {
            List<Voter> voters = dbHelper.getVotersByConstituency(selectedConstituency);
            displayVotersList(voters, "Voters in " + selectedConstituency);
        }
    }

    private void searchVoter() {
        String voterId = editTextVoterId.getText().toString().trim();
        
        if (!voterId.isEmpty()) {
            Voter voter = dbHelper.getVoterById(voterId);
            
            if (voter != null) {
                textViewResult.setText("Voter Details:\n\n" + voter.toString());
            } else {
                textViewResult.setText("No voter found with ID: " + voterId);
                Toast.makeText(this, "Voter not found", Toast.LENGTH_SHORT).show();
            }
        } else {
            Toast.makeText(this, "Please enter voter ID", Toast.LENGTH_SHORT).show();
        }
    }

    private void confirmDeleteVoter() {
        String voterId = editTextVoterId.getText().toString().trim();
        
        if (!voterId.isEmpty()) {
            Voter voter = dbHelper.getVoterById(voterId);
            
            if (voter != null) {
                new AlertDialog.Builder(this)
                    .setTitle("Confirm Delete")
                    .setMessage("Are you sure you want to delete voter:\n" + voter.getName() + " (" + voterId + ")?")
                    .setPositiveButton("Delete", (dialog, which) -> deleteVoter(voterId))
                    .setNegativeButton("Cancel", null)
                    .show();
            } else {
                Toast.makeText(this, "Voter not found", Toast.LENGTH_SHORT).show();
            }
        } else {
            Toast.makeText(this, "Please enter voter ID", Toast.LENGTH_SHORT).show();
        }
    }

    private void deleteVoter(String voterId) {
        int result = dbHelper.deleteVoter(voterId);
        
        if (result > 0) {
            Toast.makeText(this, "Voter deleted successfully", Toast.LENGTH_SHORT).show();
            editTextVoterId.setText("");
            displayAllVoters();
        } else {
            Toast.makeText(this, "Failed to delete voter", Toast.LENGTH_SHORT).show();
        }
    }

    private void showVotingStats() {
        VotingStats stats = dbHelper.getVotingStats();
        textViewResult.setText(stats.toString());
    }

    private void displayVotersList(List<Voter> voters, String title) {
        StringBuilder sb = new StringBuilder();
        sb.append(title).append(":\n\n");
        
        if (!voters.isEmpty()) {
            for (int i = 0; i < voters.size(); i++) {
                sb.append((i + 1)).append(". ").append(voters.get(i).toString()).append("\n\n");
            }
        } else {
            sb.append("No voters found");
        }
        
        textViewResult.setText(sb.toString());
    }
}

Key Features Implemented:

1. Insert and Update: Student management with complete CRUD operations
2. Delete and View: Voter management with search and filtering capabilities
3. Database Design: Proper SQLite implementation with relationships
4. User Interface: Intuitive forms and data display
5. Data Validation: Input validation and error handling
6. Sample Data: Pre-populated data for testing
7. Statistics: Voting statistics and analytics